﻿using System;
using Microsoft.VisualStudio.TestTools.UnitTesting;
using DataProcess;
using DataAccess;
using System.Data.SqlClient;
using System.Drawing;
using System.IO;
using System.Text.RegularExpressions;
using System.Data;
using System.Diagnostics;
namespace UnitTest
{
    [TestClass]
    public class UnitTest1
    {
        //[TestMethod]
        public void TestMethod1()
        {
            frmColor a = new frmColor();
            a.ConvertToHtml(@"该部分内容囊括了21项安慰剂空白对照研究（包括LIFT研究）中所记录的不良反应，在这些研究中有4079名女性受试者接受了治疗剂量的替勃龙（1.25mg或2.5mg）治疗，3476名女性受试者接受了安慰剂的治疗。这些研究的治疗期在2个月～4.5年不等。表1 列出替勃龙治疗组与安慰剂对照组之间具有统计学差异的不良反应。
表 1&nbsp; 替勃龙的不良反应
<table>
 <tr>
  <td>
  系统器官分类
  </td>
  <td>
  常见
  ＞1%，＜10%
  </td>
  <td>
  少见
  ＞0.1%，＜1%
  </td>
 </tr>
 <tr>
  <td>
  胃肠道
  </td>
  <td>
  下腹痛
  </td>
  <td>
  &nbsp;
  </td>
 </tr>
 <tr>
  <td>
  皮肤和皮下组织
  </td>
  <td>
  毛发生长异常
  </td>
  <td>
  痤疮
  </td>
 </tr>
 <tr>
  <td>
  生殖系统和乳房
  </td>
  <td>
  阴道分泌物
  子宫内膜壁增厚
  绝经后出血
  乳房触痛
  生殖器搔痒
  阴道念珠菌病
  阴道出血
  盆腔疼痛
  子宫颈异常
  生殖器异常分泌物
  外阴阴道炎
  </td>
  <td>
  乳房不适
  真菌感染
  阴道真菌感染
  乳头疼痛
  </td>
 </tr>
 <tr>
  <td>
  健康检查
  </td>
  <td>
  体重增加
  宫颈涂片检查异常*
  </td>
  <td>
  &nbsp;
  </td>
 </tr>
</table>a");
        }
        //[TestMethod]
        public void ReadSmallPicture()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "172.18.7.46";
            builder.UserID = "dmp";
            builder.Password = "medicom@dmp";
            builder.InitialCatalog = "dmp";
            Database db = new Database("release", "System.Data.SqlClient", builder.ConnectionString);
            string path = @"d:\pi";
            int maxSize = 70;
            int minSize = 59;

            var reader = db.ExecuteReader("SELECT a.*,b.is_delete  FROM [pi_images] a left join pi_info b on a.piid=b.piid");//pi_errata_images

            while (reader.Read())
            {
                byte[] imgByte = (byte[])reader["img_content"];
                MemoryStream stream = new MemoryStream(imgByte);
                Image img = Image.FromStream(stream);
                if (img.Width < maxSize && img.Width > minSize && img.Height < maxSize && img.Height > minSize)
                {
                    File.WriteAllBytes(Path.Combine(path, reader["is_delete"].ToString() + "_" + reader["piid"].ToString() + "_" + reader["pkid"].ToString() + "_" + reader["img_name"].ToString()), imgByte);
                }
                img.Dispose();
                stream.Dispose();
            }
            reader.Close();
        }


        public void ReadBigPicture()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "172.18.7.46";
            builder.UserID = "dmp";
            builder.Password = "medicom@dmp";
            builder.InitialCatalog = "dmp";
            Database db = new Database("release", "System.Data.SqlClient", builder.ConnectionString);
            string path = @"d:\pi";
            int minSize = 600;

            var reader = db.ExecuteReader("SELECT a.*,b.is_delete  FROM [pi_images] a left join pi_info b on a.piid=b.piid");//pi_errata_images

            while (reader.Read())
            {
                byte[] imgByte = (byte[])reader["img_content"];
                MemoryStream stream = new MemoryStream(imgByte);
                Image img = Image.FromStream(stream);
                if (img.Width >= minSize)
                {
                    File.WriteAllBytes(Path.Combine(path, reader["is_delete"].ToString() + "_" + reader["piid"].ToString() + "_" + reader["pkid"].ToString() + "_" + reader["img_name"].ToString()), imgByte);
                }
                img.Dispose();
                stream.Dispose();
            }
            reader.Close();
        }


        public void ReadChemistryPicture()
        {
            Regex imgRegex = new Regex("<img\\s+.*?src=['\"](.*?)['\"].*?>");
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "172.18.7.46";
            builder.UserID = "dmp";
            builder.Password = "medicom@dmp";
            builder.InitialCatalog = "dmp";
            Database db = new Database("release", "System.Data.SqlClient", builder.ConnectionString);
            string path = @"d:\pi";

            var reader = db.ExecuteReader("SELECT a.* FROM [pi_content] a left join pi_info b on a.piid=b.piid where a.is_valid=1 and b.is_delete=0 and (a.phname='成分' or a.phname='结构式' or a.phname='分子式')");//pi_errata_images

            while (reader.Read())
            {
                var matches = imgRegex.Matches(reader["content"].ToString());
                foreach (Match matche in matches)
                {
                    var reader1 = db.ExecuteReader("SELECT * FROM [pi_images] where piid=@piid and img_name=@img_name", new Parameter("piid", reader["piid"]), new Parameter("img_name", matche.Groups[1].Value));
                    while (reader1.Read())
                    {
                        byte[] imgByte = (byte[])reader1["img_content"];
                        File.WriteAllBytes(Path.Combine(path, reader1["piid"].ToString() + "_" + reader1["pkid"].ToString() + "_" + reader1["img_name"].ToString()), imgByte);
                    }
                    reader1.Close();
                    reader1.Dispose();
                }
            }
            reader.Close();
            reader.Dispose();
        }

        [TestMethod]
        public void ReadRedundantPicture()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "172.18.7.46";
            builder.UserID = "dmp";
            builder.Password = "medicom@dmp";
            builder.InitialCatalog = "dmp";
            Database db = new Database("release", "System.Data.SqlClient", builder.ConnectionString);
            string path = @"d:\pi";

            var imgData = db.ExecuteDataTable("SELECT a.pkid,a.piid,a.img_name FROM [pi_images] a left join pi_info b on a.piid=b.piid where b.is_delete=0");//pi_errata_images

            for (int i = 0; i < imgData.Rows.Count; i++)
            {
                //<img\s+[^<>]*?src=['\"]?1741\.jpg['\"]?[^<>]*?>
                int cnt = (int)db.ExecuteScalar("SELECT count(*) as cnt FROM [pi_content] where piid=@piid and is_valid=1 and content like '%<img%' and dbo.IsMatch(content,'<img\\s+[^<>]*?src=[''\"]?" + imgData.Rows[i]["img_name"].ToString().Replace(".", "\\.") + "[''\"]?[^<>]*?>')=1", new Parameter("piid", imgData.Rows[i]["piid"]));
                if (cnt == 0)
                {
                    //byte[] imgByte = (byte[])imgData.Rows[i]["img_content"];
                    //File.WriteAllBytes(Path.Combine(path, imgData.Rows[i]["piid"].ToString() + "_" + imgData.Rows[i]["pkid"].ToString() + "_" + imgData.Rows[i]["img_name"].ToString()), imgByte);
                    db.ExecuteNonQuery("INSERT INTO [dbo].[pi_images_redundant] (pkid,img_name) VALUES (@pkid,@img_name)", new Parameter("pkid", imgData.Rows[i]["pkid"]), new Parameter("img_name", imgData.Rows[i]["img_name"]));
                }
            }
            Debug.WriteLine("多余图片读取完成！");
            return;
            var reader = db.ExecuteReader("select a.pkid,a.piid,a.img_name,a.img_content from pi_images a inner join pi_images_redundant b on a.pkid=b.pkid");
            while (reader.Read())
            {
                byte[] imgByte = (byte[])reader["img_content"];
                File.WriteAllBytes(Path.Combine(path, reader["piid"].ToString() + "_" + reader["pkid"].ToString() + "_" + reader["img_name"].ToString()), imgByte);
            }
            db.ExecuteNonQuery("truncate table pi_images_redundant");
            reader.Close();
            reader.Dispose();
        }

        
        public void test()
        {
            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = "172.18.7.46";
            builder.UserID = "dmp";
            builder.Password = "medicom@dmp";
            builder.InitialCatalog = "Product_MC";
            Database db = new Database("release", "System.Data.SqlClient", builder.ConnectionString);
            var reader = db.ExecuteReader("SELECT [i_docid],[m_pi_content] from web_pi_content");

            //var dt = db.ExecuteDataTable("SELECT * FROM pi_images");
            while (reader.Read())
            {
                string content = reader["m_pi_content"].ToString();
                if (content.IndexOf("<img") > -1)
                {
                    var matches = Regex.Matches(content, "<img\\s+[^<>]*?src=['\\\"]?([^'\\\"]*)['\\\"]?[^<>]*?>");

                    for (int i = 0; i < matches.Count; i++)
                    {
                        var ret = (int)db.ExecuteScalar("select count(*) from web_pi_images where i_docid=@i_docid and picturename=@picturename", new Parameter("i_docid", reader["i_docid"]), new Parameter("picturename", matches[i].Groups[1].Value));
                        if (ret == 0)
                        {
                            Debug.WriteLine(reader["i_docid"] + "|" + matches[i].Groups[1].Value);
                        }
                    }
                }
            }
            //SqlConnection conn = new SqlConnection(builder.ConnectionString);
            //using (SqlBulkCopy bulkCopy = new SqlBulkCopy(conn))
            //{
            //    bulkCopy.DestinationTableName =
            //        "dbo.pi_images";

            //    try
            //    {
            //        // Write from the source to the destination.
            //        bulkCopy.WriteToServer(dt);
            //    }
            //    catch (Exception ex)
            //    {
            //        Console.WriteLine(ex.Message);
            //    }
            //}

        }
    }
}
